package arg

import (
	"fmt"
	"github.com/gurkankaymak/hocon"
	"os"
	"reflect"
	"runtime"
	"strings"
)

/*
##########################setp1：
	create or replace procedure p_dumpsql21(columnSplist varchar2,rowsSplist varchar2,begintime varchar2,endtime varchar2,user_name varchar2) as
  sqlid  varchar2(50);

  LVC_SQL_TEXT      VARCHAR2(32000);
  LVC_ORIG_SQL_TEXT VARCHAR2(32000);
  LVC_BIND          VARCHAR2(200);
  executions        number;
  elaptimePerms     number;
  WAS_CAPTURED      varchar2(3);
  sqlOutSwitch      varchar2(3);
  xxx               varchar2(32000);
  cursor cursql is select t.sql_id,t.SQL_FULLTEXT,executions,round((elapsed_time/1000) / decode(executions, NULL, 1, 0, 1, executions),2) elaptime_perms from gv$sqlarea t where t.command_type in (2,3,6,7) and t.parsing_schema_name=user_name || xxx;

  CURSOR C1 IS
 select CHILD_NUMBER, NAME, POSITION, DATATYPE_STRING, VALUE_STRING,WAS_CAPTURED, sql_id
   from GV$SQL_BIND_CAPTURE
  where sql_id = sqlid
    and child_number = (select child_number
                          from (select child_number
                                  from GV$SQL_BIND_CAPTURE
                                 where sql_id = sqlid
                                 order by last_captured desc, inst_id)
                         where rownum <= 1)
  order by position;

begin
   DBMS_OUTPUT.ENABLE(buffer_size => null);
     if begintime = '0' and endtime = '0' then
	 xxx := '';
      end if;
  if begintime = '0' and endtime != '0' then
    xxx := 't.LAST_ACTIVE_TIME >= to_date(begintime,''yyyy-mm-dd'')';
  end if;
  if begintime != '0' and endtime = '0' then
     xxx := 't.LAST_ACTIVE_TIME <= to_date(endtime,''yyyy-mm-dd'')';
  end if;
    if begintime != '0' and endtime = '0' then
	 xxx := 't.LAST_ACTIVE_TIME between to_date(begintime,''yyyy-mm-dd'') and to_date(endtime,''yyyy-mm-dd'')';
  end if;

  for cur in cursql loop
	 executions:=cur.executions;
	 elaptimePerms:=cur.elaptime_perms;
     sqlid:=cur.sql_id;
     LVC_ORIG_SQL_TEXT := cur.sql_fulltext;
     LVC_SQL_TEXT :=NULL;
	 sqlOutSwitch :='yes';
     FOR R1 IN C1 LOOP
          LVC_SQL_TEXT :=LVC_ORIG_SQL_TEXT;
          IF R1.NAME LIKE ':SYS_B_%' THEN
             LVC_BIND := ':"'||substr(R1.NAME,2)||'"';
          ELSE
             LVC_BIND := R1.NAME;
          END IF;
		  IF R1.WAS_CAPTURED = 'NO' THEN
			  sqlOutSwitch := 'no';
		  END IF;
          IF r1.VALUE_STRING IS NOT NULL THEN
            IF R1.DATATYPE_STRING = 'NUMBER' THEN
              LVC_SQL_TEXT := REGEXP_REPLACE(LVC_SQL_TEXT, LVC_BIND, R1.VALUE_STRING,1,1,'i');
            ELSIF R1.DATATYPE_STRING LIKE 'VARCHAR%' THEN
              LVC_SQL_TEXT := REGEXP_REPLACE(LVC_SQL_TEXT, LVC_BIND, ''''||R1.VALUE_STRING||'''',1,1,'i');
            ELSE
              LVC_SQL_TEXT := REGEXP_REPLACE(LVC_SQL_TEXT, LVC_BIND, ''''||R1.VALUE_STRING||'''',1,1,'i');
            END IF;
          ELSE
             LVC_SQL_TEXT := REGEXP_REPLACE(LVC_SQL_TEXT, LVC_BIND, 'NULL',1,1,'i');
          END IF;
        END LOOP;

	   if sqlOutSwitch = 'yes' then
	          if LVC_SQL_TEXT is not null then
				DBMS_OUTPUT.PUT_LINE(user_name||columnSplist||sqlid||columnSplist||executions||columnSplist||elaptimePerms||columnSplist||LVC_SQL_TEXT||rowsSplist);
			  else
				DBMS_OUTPUT.PUT_LINE(user_name||columnSplist||sqlid||columnSplist||executions||columnSplist||elaptimePerms||columnSplist||LVC_ORIG_SQL_TEXT||rowsSplist);
			  end if ;
	   end if;


  end loop;
end;
/

##############setup2:
 -- 提取访问ABS库的用户('ABS_GENERL','ABS_ARCHIVE','ABS_PRODUCT','ABS_ORDER','ABS_STORE')
    5个库逐个分析
select parsing_schema_name,count(*)
  from v$sql
 where to_date(last_load_time,'yyyy-mm-dd/hh24:mi:ss') > trunc(sysdate - 3) and
       exists(select 1 from dba_tables b
                      where owner = 'ABS_GENERL' and
                            lower(sql_text) like '%'||lower(b.table_name)||'%')
group by parsing_schema_name;

##############setup3：
SQL> 命令行执行
set serveroutput on size 1000000
SET VERIFY OFF
set echo off
set long 9999999
set lines 1000 pages 0  heading on
set trimspool  on
set trimout on
set term off
set termout off
spool '/tmp/hot7.sql';
EXEC p_dumpsql21('xxx','xxx','2021-08-18','2023-09-5','PCMS');
spool off
set serveroutput off

SQL> 文件执行
cat>/root/execGetHotSql.sql<<EOF
set serveroutput on size 1000000
SET VERIFY OFF
set echo off
set long 9999999
set lines 1000 pages 0  heading on
set trimspool  on
set trimout on
set term off
set termout off
spool '/tmp/hot6.sql';
EXEC p_dumpsql13('xxx','xxx','2021-08-18','2023-09-5','PCMS');
spool off
set serveroutput off
EOF
SQL> @/root/execGetHotSql.sql

####################setup4：
	awk '{if ($1 == "SQL>" || $1 == "PL/SQL"|| $1 == "") {next} {print}}' hot6.sql >hot6-1.sql
*/

func (rc *ConfigParameter) filePathGeneral(path, file string) (fileP string) {
	switch runtime.GOOS {
	case "linux":
		fileP = fmt.Sprintf("%s/%s", path, file)
	case "windows":
		fileP = fmt.Sprintf("%s\\%s", path, file)
	}
	return
}

// 针对hotsql input file进行参数获取和判断
func (rc *ConfigParameter) hotsqlInputFile(f3 *hocon.Config, s string) (s1 string) {
	var ff = map[string]any{"splist": map[string]string{}, "name": map[string][]string{}}
	if !rc.mustExistParameter(f3, s, ff) {
		vlog := fmt.Sprintf("%v report: When specifying that the input in the hotsql parameter is file, the {splist and name} parameters must be set", ProcessName)
		WLog.Error(vlog)
		fmt.Println(fmt.Sprintf("%v report: parameter init fail,please check the log for details!", ProcessName))
		os.Exit(1)
	}
	var s2 []string
	for k, v := range ff {
		if rc.mustExistParameterValue(f3.GetConfig(s), k) {
			event := "hotsql_input"
			vlog := fmt.Sprintf("(%d) %s %v report: The {%s} setting in the {file} parameter sub-option parameter cannot be empty.", ProcessName, rc.LogThreadSeq, event, k)
			WLog.Error(vlog)
			fmt.Println(fmt.Sprintf("%v report: parameter init fail,please check the log for details!", ProcessName))
			os.Exit(1)
		} else {
			if strings.EqualFold(k, "splist") {
				if r1 := rc.getHoconConfigVal(f3.GetConfig(s), k, reflect.TypeOf(v).String()); r1 != nil {
					s2 = append(s2, rc.anyToJsonString(k, r1))
				}
			} else if strings.EqualFold(k, "name") {
				var r2 []string
				if r1 := rc.getHoconConfigVal(f3.GetConfig(s), k, reflect.TypeOf(v).String()); r1 != nil {
					for k1, v1 := range r1.(map[string][]string) {
						for _, v2 := range v1 {
							r2 = append(r2, rc.filePathGeneral(k1, v2))
						}
					}
				}
				s2 = append(s2, rc.anyToJsonString(k, r2))
			}
		}
	}
	return strings.Join(s2, ",")
}

// 针对hotsql input oracle进行参数获取和判断
func (rc *ConfigParameter) hotsqlInputOracle(f3 *hocon.Config, s string, indef map[string]any) (s1 string) {
	rc.event = "[hotsql_input]"
	var (
		s2 []string
	)
	if strings.EqualFold(f3.GetObject(s).String(), "{}") {
		for k, v := range indef {
			s2 = append(s2, rc.anyToJsonString(k, v))
		}
		s1 = fmt.Sprintf("%s", strings.Join(s2, ","))
		return
	}
	for k, v := range indef {
		if r1 := rc.getHoconConfigVal(f3.GetConfig(s), k, reflect.TypeOf(v).String()); r1 != nil {
			s2 = append(s2, rc.anyToJsonString(k, r1))
		} else {
			vlog := fmt.Sprintf("(%d) %s %v report: The sub-option {%s} parameter setting of the {oracle} parameter is incorrect, and the default value will be used.", rc.LogThreadSeq, rc.event, ProcessName, k)
			WLog.Warn(vlog)
			s2 = append(s2, rc.anyToJsonString(k, v))
		}
	}
	s1 = strings.Join(s2, ",")
	return
}

// 获取hotsql input的参数检查，f3表示上一级的hocon config，st表示当前key
func (rc *ConfigParameter) hotsqlInput(f3 *hocon.Config, st string, intdef map[string]any) (s string) {
	keyMap := rc.hoconfMapKey(f3, st)
	if len(keyMap) > 1 {
		vlog := fmt.Sprintf("(%d) %s %v report: Only one sub-option {file、oracle} can be configured in the input parameter.", rc.LogThreadSeq, rc.event, ProcessName)
		WLog.Error(vlog)
		fmt.Println(fmt.Sprintf("%v report: parameter init fail,please check the log for details!", ProcessName))
		os.Exit(1)
	}
	if len(keyMap) == 0 {
		var s1 []string
		for k, v := range intdef {
			s1 = append(s1, rc.anyToJsonString(k, v))
		}
		s = fmt.Sprintf("\"%s\":{%s}", st, strings.Join(s1, ","))
		vlog := fmt.Sprintf("(%d) %s The sub-option {file、oracle} is not configured in the input parameter, and the default parameter {oracle} will be used", rc.LogThreadSeq, rc.event)
		WLog.Warn(vlog)
		return
	}

	var s2 []string
	for k := range keyMap {
		switch k {
		case "file":
			s2 = append(s2, rc.anyToJsonString("fay", k), rc.hotsqlInputFile(f3.GetConfig(st), k))
		case "oracle":
			for k1, v1 := range intdef {
				if v1 == k {
					delete(intdef, k1)
					s2 = append(s2, rc.anyToJsonString(k1, v1))
				}
			}
			s2 = append(s2, rc.hotsqlInputOracle(f3.GetConfig(st), k, intdef))
		}
	}
	return fmt.Sprintf("\"%s\":{%s}", st, strings.Join(s2, ","))
}

// 针对hotsql input oracle进行参数获取和判断
func (rc *ConfigParameter) hotsqlOutputGreatdb(f3 *hocon.Config, s string, outdef map[string]any) (s2 string) {
	rc.event = "[hotsql_output]"
	rc.SecondaryL.RulesV.HotSqlV.Output.OutPutFay = s
	var s1 []string
	if strings.EqualFold(f3.GetObject(s).String(), "{}") {
		s1 = append(s1, rc.anyToJsonString("fay", s))
		for k, v := range outdef {
			s1 = append(s1, rc.anyToJsonString(k, v))
		}
		s2 = fmt.Sprintf("%s", strings.Join(s1, ","))
		return
	}
	s1 = append(s1, rc.anyToJsonString("fay", s))
	for k, v := range outdef {
		if r1 := rc.getHoconConfigVal(f3.GetConfig(s), k, reflect.TypeOf(v).String()); r1 != nil {
			s1 = append(s1, rc.anyToJsonString(k, r1))
		} else {
			vlog := fmt.Sprintf("(%d) %s %v report: The sub-option {%s} parameter setting of the {greatdb} parameter is incorrect, and the default value will be used.", rc.LogThreadSeq, rc.event, ProcessName, k)
			WLog.Warn(vlog)
			s1 = append(s1, rc.anyToJsonString(k, v))
		}
	}
	s2 = fmt.Sprintf("%s", strings.Join(s1, ","))
	return
}
func (rc *ConfigParameter) hotsqlOutput(f3 *hocon.Config, st string, outdef map[string]any) (s string) {
	rc.event = "[hotsql_output]"
	keyMap := rc.hoconfMapKey(f3, st)
	if len(keyMap) > 1 {
		vlog := fmt.Sprintf("(%d) %s %v report: Only one sub-option {file、greatdb} can be configured in the output parameter.", rc.LogThreadSeq, rc.event, ProcessName)
		WLog.Error(vlog)
		fmt.Println(fmt.Sprintf("%v report: parameter init fail,please check the log for details!", ProcessName))
		os.Exit(1)
	}
	if len(keyMap) == 0 {
		var s1 []string
		for k, v := range outdef {
			s1 = append(s1, rc.anyToJsonString(k, v))
		}
		s = fmt.Sprintf("\"%s\":{%s}", st, strings.Join(s1, ","))
		vlog := fmt.Sprintf("(%d) %s The sub-option {file、greatdb} is not configured in the output parameter, and the default parameter {greatdb} will be used", rc.LogThreadSeq, rc.event)
		WLog.Warn(vlog)
		return
	}
	var s2 []string
	for k := range keyMap {
		switch k {
		case "file":

		case "greatdb":
			for k1, v1 := range outdef {
				if v1 == k {
					delete(outdef, k1)
				}
			}
			s2 = append(s2, rc.hotsqlOutputGreatdb(f3.GetConfig(st), k, outdef))
		}
	}
	s = fmt.Sprintf("\"%s\":{%s}", st, strings.Join(s2, ","))
	return
}

type hotsqlLevel struct {
}

// 热sql功能参数获取
func hotSqlRuleParameter(hocon *hocon.Config) (HotSqlV HotSql, err error) {
	//var (
	//	f3       = rc.FirstL1.Rules
	//	//f4       = rc.FirstL1.Rules.ToConfig().GetConfig(rc.getObjectName(checkMod))
	//	jsonData string
	//	sf       = map[string]map[string]any{"input": {"fay": "oracle", "startTime": time.Now().AddDate(0, 0, -1).Format("2006-01-02"), "endTime": time.Now().Format("2006-01-02")},
	//		"output": {"fay": "greatdb", "timeOut": 10}}
	//	l1 = f3.ToConfig().GetStringMap(rc.getObjectName(checkMod))
	//	t2 []string
	//)
	////rc.event = "[hotsql parameter init]"
	////rc.SecondaryL.RulesV.CheckMode = rc.getObjectName(checkMod)
	////rc.SecondaryL.FunctionalMode = rc.getObjectName(checkMod)
	//if len(l1) == 0 {
	//	for k, v := range sf {
	//		var t1 []string
	//		for k1, v1 := range v {
	//			t1 = append(t1, rc.anyToJsonString(k1, v1))
	//		}
	//		t2 = append(t2, fmt.Sprintf("\"%s\":{%s}", k, strings.Join(t1, ",")))
	//	}
	//	jsonData = fmt.Sprintf("{%s}", strings.Join(t2, ","))
	//	if err := json.Unmarshal([]byte(jsonData), &rc.SecondaryL.RulesV.HotSqlV); err != nil {
	//		rc.vlog = fmt.Sprintf("(%d) %s hotsql parameter init fail. json unmarshal fail error info is %s. json data is {%s}", rc.LogThreadSeq, rc.event, err, jsonData)
	//		global.Wlog.Error(rc.vlog)
	//		fmt.Println("%v report: parameter init fail,please check the log for details!")
	//		os.Exit(1)
	//	}
	//	return
	//}
	//for k, v := range sf {
	//	if _, ok := l1[k]; ok {
	//		switch k {
	//		case "input":
	//			t2 = append(t2, rc.hotsqlInput(f4, k, sf[k]))
	//		case "output":
	//			t2 = append(t2, rc.hotsqlOutput(f4, k, sf[k]))
	//		}
	//	} else {
	//		var t1 []string
	//		for k1, v1 := range v {
	//			t1 = append(t1, rc.anyToJsonString(k1, v1))
	//		}
	//		t2 = append(t2, fmt.Sprintf("\"%s\":{%s}", k, strings.Join(t1, ",")))
	//	}
	//}
	//jsonData = strings.ReplaceAll(fmt.Sprintf("{%s}", strings.Join(t2, ",")), "\\", "\\\\")
	//if err := json.Unmarshal([]byte(jsonData), &rc.SecondaryL.RulesV.HotSqlV); err != nil {
	//	rc.vlog = fmt.Sprintf("(%d) %s hotsql parameter init fail. json unmarshal fail error info is %s. json data is {%s}", rc.LogThreadSeq, rc.event, err, jsonData)
	//	global.Wlog.Error(rc.vlog)
	//	fmt.Println("report: parameter init fail,please check the log for details!")
	//	os.Exit(1)
	//}
	//rc.SecondaryL.RulesV.HotSqlV.Output.OutTime = rc.SecondaryL.RulesV.HotSqlV.Output.OutTime * time.Second
	HotSqlV.ActiveSwitch = ActiveSwitch{TableAccessPriInit: false, TableStructInit: false, GlobalParameterInit: false, TableIndexInit: false, TableIndexFilter: false, TableTruncate: false, TableRecover: false, PartitionInit: false}
	return
}

func hotSqlParameterCheck(s *SecondaryLevel) error {
	//var (
	//	Event              = "[hotsql parameter check]"
	//	LogThreadSeq int64 = 0
	//	hotSqloutPut       = map[string]int{"file": 1, "xls": 1, "greatdb": 1}
	//	err          error
	//)
	//if _, ok := hotSqloutPut[strings.ToLower(rc.SecondaryL.RulesV.HotSqlV.Output.OutPutFay)]; !ok {
	//	global.Wlog.Debug(fmt.Sprintf("(%d) %s The current parameter %s configuration error will use the default value {%v}", LogThreadSeq, Event, "rules.hotsql.output", rc.SecondaryL.RulesV.HotSqlV.Output))
	//	rc.SecondaryL.RulesV.HotSqlV.Output.OutPutFay = "file"
	//}
	//if strings.EqualFold(rc.SecondaryL.RulesV.HotSqlV.Output.OutPutFay, "file") {
	//	if rc.SecondaryL.RulesV.HotSqlV.Output.FileFin, err = os.OpenFile(C("dataFix", "hotSqlPlayBack.result"), os.O_CREATE|os.O_TRUNC|os.O_RDWR, 0666); err != nil {
	//		fmt.Println(err)
	//		os.Exit(1)
	//	}
	//}
	return nil
}
